contactnumber
Table: contactnumber
The contactnumber table manages contact number information linked to individuals or entities. It includes details about the type, ownership, and relationships associated with each contact number, along with metadata for audit purposes.
Columns
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
| contactNumberId | int(11) | NOT NULL AUTO_INCREMENT | Unique identifier for the contact number. |
| createdDate | datetime | Date the record was created. | |
| description | varchar(255) | Additional information about the contact number. | |
| lastEditedDate | datetime | Date of the last modification. | |
| mappedId | int(11) | NOT NULL | Links to related records in idmapper. |
| number | varchar(20) | NOT NULL | The contact number itself. |
| numberType | varchar(20) | NOT NULL DEFAULT 'UNKNOWN' | Type of the contact number (e.g., mobile, home). |
| accountType | varchar(20) | Associated account type for the number. | |
| ownerRelation | varchar(100) | Relationship of the owner (e.g., parent, sibling). | |
| ownerFirstName | varchar(30) | First name of the contact number owner. | |
| ownerLastName | varchar(30) | Last name of the contact number owner. | |
| createdByUserId | int(11) | User who created the record (foreign key). | |
| lastEditedByUserId | int(11) | User who last edited the record (foreign key). | |
| telelineType | varchar(45) | NOT NULL DEFAULT 'UNKNOWN' | Telecommunication line type (e.g., landline, VoIP). |
| voided | tinyint(1) | DEFAULT '0' | Indicates if the record is voided (0 = active). |
| voidedReason | varchar(45) | Reason for voiding the record. | |
| voidedDate | datetime | Date the record was voided. |
Indexes
- Primary Index
- contactNumberId: Ensures unique identification for each record.
- Foreign Key Indexes
- contactnumber_mappedId_idmapper_mappedId_FK: Links mappedId to the idmapper table.
- contactnumber_lastEditedByUserId_user_mappedId_FK: Links lastEditedByUserId to the user table (mappedId).
- contactnumber_createdByUserId_user_mappedId_FK: Links createdByUserId to the user table (mappedId).
- Relational and Search Indexes
- contactnumber_ownerRelation_relationship_relationshipId_FK: For queries based on owner relationships.
- idx_contactnumber_mappedId_numberType: Helps find child queries based on mappedId and numberType.
- idx_contactnumber_number: Optimized for searching by contact number.
- Timestamp Indexes
- contactnumber_createdDate: Supports date-based searches for record creation.
Foreign Key Relations
- contactnumber_mappedId_idmapper_mappedId_FK
- Links mappedId to the idmapper table (mappedId), enabling integration with other records.
- contactnumber_createdByUserId_user_mappedId_FK
- Links createdByUserId to the user table (mappedId) for tracking record creation.
- contactnumber_lastEditedByUserId_user_mappedId_FK
- Links lastEditedByUserId to the user table (mappedId) for tracking edits.
Usage Notes
- Contact Number Management: Supports storage and categorization of multiple contact numbers for a single entity.
- Audit and Tracking: Columns like createdDate, lastEditedDate, createdByUserId, and lastEditedByUserId ensure auditability.
- Voiding Mechanism: Records can be voided for deactivation while retaining historical data (voided, voidedReason, and voidedDate).
- Hierarchical Relationships: The ownerRelation and mappedId enable representing complex relationships between entities.